package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInventory;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import org.utils.AnalogConvertor_RU;

import extend.org.compiere.utils.Util;

public class MM_Form_Inv10 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	private Integer p_mol, p_chiefComm, p_memberComm1, p_memberComm2, p_memberComm3, p_memberComm4, p_memberComm5;
	
	Date p_DateDoc, p_DateDocBase;
	
	String ResultPath, p_DocumentBaseNo, p_DocumentNo;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("M_Inventory_ID"))
			p_DocumentNo = (String) para[i].getParameter();
		else if (name.equals("mol"))
			p_mol = para[i].getParameterAsInt();
		else if (name.equals("chiefComm"))
			p_chiefComm = para[i].getParameterAsInt();
		else if (name.equals("memberComm1"))
			p_memberComm1 = para[i].getParameterAsInt();
		else if (name.equals("memberComm2"))
			p_memberComm2 = para[i].getParameterAsInt();
		else if (name.equals("memberComm3"))
			p_memberComm3 = para[i].getParameterAsInt();
		else if (name.equals("memberComm4"))
			p_memberComm4 = para[i].getParameterAsInt();
		else if (name.equals("memberComm5"))
			p_memberComm5 = para[i].getParameterAsInt();
		else if (name.equals("DocumentBaseNo"))
			p_DocumentBaseNo = (String) para[i].getParameter();
		else if (name.equals("DateDoc"))
			p_DateDoc = (Date) para[i].getParameter();
		else if (name.equals("DateDocBase"))
			p_DateDocBase = (Date) para[i].getParameter();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Inv10_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 12) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		mergeStyle.setFont(wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex()));
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(21).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(49).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
//		MInventory inv = new MInventory(m_ctx, p_Record_ID, get_TrxName());
		sheet.getRow(12).getCell(44).setCellValue(p_DocumentNo);
		sheet.getRow(12).getCell(54).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_DateDoc)));
		sheet.getRow(27).getCell(21).setCellValue((new SimpleDateFormat("dd").format(p_DateDocBase)));
		sheet.getRow(27).getCell(24).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDocBase)));
		sheet.getRow(27).getCell(32).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDocBase)));
		sheet.getRow(27).getCell(38).setCellValue(p_DocumentBaseNo);
		
		sheet.getRow(28).getCell(32).setCellValue((new SimpleDateFormat("dd").format(p_DateDoc)));
		sheet.getRow(28).getCell(35).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDoc)));
		sheet.getRow(28).getCell(43).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDoc)));
		
		//nacalo inventory
//		sheet.getRow(30).getCell(12).setCellValue((new SimpleDateFormat("dd").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(15).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(23).setCellValue((new SimpleDateFormat("yyyy").format(inv.getMovementDate())));
		//konets inventory
//		sheet.getRow(30).getCell(48).setCellValue((new SimpleDateFormat("dd").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(51).setCellValue((new SimpleDateFormat("MMMMM").format(inv.getMovementDate())));
//		sheet.getRow(30).getCell(59).setCellValue((new SimpleDateFormat("yyyy").format(inv.getMovementDate())));
		
		
		//Document Header
		String sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_mol;
		int id = DB.getSQLValue(get_TrxName(), sqlheader);
//		sheet.getRow(24).getCell(10).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(24).getCell(29).setCellValue((new MLocator(m_ctx, p_mol, get_TrxName()).getValue()));
//		sheet.getRow(73).getCell(29).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(73).getCell(48).setCellValue((new MLocator(m_ctx, p_mol, get_TrxName()).getValue()));
		sheet.getRow(74).getCell(4).setCellValue((new SimpleDateFormat("dd").format(p_DateDoc)));
		sheet.getRow(74).getCell(7).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDoc)));
		sheet.getRow(74).getCell(15).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDoc)));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_chiefComm;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(52).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(52).getCell(32).setCellValue((new MBPartner(m_ctx, p_chiefComm, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm1;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(55).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(55).getCell(32).setCellValue((new MBPartner(m_ctx, p_memberComm1, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm2;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(58).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(58).getCell(32).setCellValue((new MBPartner(m_ctx, p_memberComm2, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm3;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(61).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(61).getCell(32).setCellValue(p_memberComm3 == null ? "" : (new MBPartner(m_ctx, p_memberComm3, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm4;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(64).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(64).getCell(32).setCellValue(p_memberComm4 == null ? "" : (new MBPartner(m_ctx, p_memberComm4, get_TrxName()).getName()));
		sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm5;
		id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(67).getCell(13).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(67).getCell(32).setCellValue(p_memberComm5 == null ? "" : (new MBPartner(m_ctx, p_memberComm5, get_TrxName()).getName()));
		
		//Positions
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "with t1 as ( " + 
						" select product " + 
							" , account " + 
							" , product_number " + 
							" , uom " + 
							" , sum(remainQty) AS RemainQty " + 
							" , sum(remainAmount) AS RemainAmt " + 
						" from mm_turnover_v " + 
						" where dateacct >= '1990-01-01' and dateacct <= '" + p_DateDoc + "' " + 
						" and m_locator_id = " + p_mol + 
						" group by product " + 
							" , account " + 
							" , product_number " + 
							" , uom " + 
						" order by product_number " + 
						" ), t2 AS ( " + 
						" select product " + 
							" , account " + 
							" , product_number " + 
							" , uom  " + 
							" , (sum(RemainAmt) / sum(RemainQty)) AS CalPrice " + 
							" , RemainQty " + 
							" , RemainAmt " + 
						" from t1 " + 
						" where RemainQty <> 0 " + 
						" group by product " + 
							" , account " + 
							" , product_number " + 
							" , uom " + 
							" , RemainQty " + 
							" , RemainAmt) " + 
						" select * from t2";
		
		int row = 41;
		int no = 0, totalQty = 0;
		double totalAmt = 0;
		
		try {
			ps = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				//Shifting and Merging
				sheet.shiftRows(row, row + 34, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 2));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 15));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 16, 21));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 22, 26));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 27, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 36));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 37, 42));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 43, 50));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 56));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 57, 64));
				
				R = sheet.createRow(row);
				for(int i = 0; i <= 64; i++){
					C = R.createCell(i);
					C.setCellStyle(mergeStyle);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(++no);
				sheet.getRow(row).getCell(3).setCellValue(rs.getString("product"));
				sheet.getRow(row).getCell(16).setCellValue(rs.getString("account"));
				sheet.getRow(row).getCell(22).setCellValue(rs.getString("product_number"));
				sheet.getRow(row).getCell(27).setCellValue(rs.getString("uom"));
				sheet.getRow(row).getCell(32).setCellValue(rs.getDouble("calprice"));
				sheet.getRow(row).getCell(37).setCellValue(rs.getInt("RemainQty"));
				totalQty += rs.getInt("RemainQty");
				sheet.getRow(row).getCell(43).setCellValue(rs.getDouble("RemainAmt"));
				totalAmt += rs.getDouble("RemainAmt");
				sheet.getRow(row).getCell(51).setCellValue(rs.getInt("RemainQty"));
				sheet.getRow(row).getCell(57).setCellValue(rs.getDouble("RemainAmt"));
				
				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		
		sheet.getRow(row).getCell(37).setCellFormula("SUM(AL42:AL" + row + ")");
		sheet.getRow(row).getCell(43).setCellFormula("SUM(AR42:AR" + row + ")");
		sheet.getRow(row).getCell(51).setCellFormula("SUM(AZ42:AZ" + row + ")");
		sheet.getRow(row).getCell(57).setCellFormula("SUM(BF42:BF" + row + ")");
		
		sheet.getRow(row + 2).getCell(20).setCellValue(AnalogConvertor_RU.toString(no));
		sheet.getRow(row + 5).getCell(24).setCellValue(AnalogConvertor_RU.toString(totalQty));
		sheet.getRow(row + 8).getCell(24).setCellValue(AnalogConvertor_RU.toString(totalAmt));
		
		sheet.getRow(row + 20).getCell(39).setCellValue(no);
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}	
}
